import pyodbc
import SeleniumTestHelper


if SeleniumTestHelper.env_variable == 'dev':
    print "running DB tests for: DEV"
    our_db=("JV_DEV")
                
elif SeleniumTestHelper.env_variable == "qa":
    print "running DB tests for: QA"
    our_db=("JV_QA")
    
elif SeleniumTestHelper.env_variable == "stage":
    print "running DB tests for: STAGE"
    our_db=("JV_STAGE")
    
elif option == "prod":
    print "You are running the test in Prod. This is not allowed"    


connect_string = "DRIVER={SQL Server};SERVER=database;DATABASE=%s;" % (our_db) + "Trusted_Connection=yes"

##testing without class for now.
def sql_data_modify(sql, *inp_params):
    cnxn = pyodbc.connect(connect_string)
    cursor = cnxn.cursor()
    
    cursor.execute(sql, inp_params)
    cnxn.commit()
    #print "Number of rows affected by rejecting checkin: %s" % str(cursor.rowcount)
    
    cursor.close()
    cnxn.close()


def sql_data_fetch(sql, *inp_params):

    cnxn = pyodbc.connect(connect_string)
    cursor = cnxn.cursor()
    
    cursor.execute(sql, inp_params)
    rows = cursor.fetchall()
    
    result= []
    for row in rows:
        #print row
        result.append(row)
    #print paxs
     
    cursor.close()
    cnxn.close()
    
    try:
        result[0][:]
    except IndexError:
        result=["0_DB_Results. "]
        print result 
        return tuple(result)
    
    #print type(tuple(result))
    return tuple(result)





if __name__=='__main__':
    #trying_execute()
#    pluto_status_summary('Set to Arrive', '17')
    #pxr_checkin_ready (1100748)
    print (' ')




###ARCHIVES



#def pluto_status_summary(pax_status, icd_seasonId):
#    cnxn = pyodbc.connect(connect_string)
#    cursor = cnxn.cursor()
#    
#    
#    #
#    sql = ("""  
#   select top 5 
#ICDApplication.ICDParticipantID, ICDApplication.Email, ICDParticipantStatus.name, ICDApplication.WhenStart 
# from ICDApplication
#join ICDParticipantStatus
#on ICDApplication.ICDParticipationStatusID = ICDParticipantStatus.ICDParticipantStatusId
#where 1 =1
#
#and ICDParticipantStatus.name=?
#and ICDApplication.ICDSeasonID = ?
#order by ICDApplication.WhenStart desc """)
#    params = (pax_status, icd_seasonId)
#    cursor.execute(sql, params)
#    rows = cursor.fetchall()
#    #cnxn.commit()
#    #print "Number of rows affected by summary query: %s" % str(cursor.rowcount)
#    paxs= []
#    for row in rows:
#        #print row
#        paxs.append((row[1], row[0]))
#    #print paxs
#     
#    cursor.close()
#    cnxn.close()
#    return paxs




#def pxr_checkin_reject(checkIn_pax_id):
#    cnxn = pyodbc.connect(connect_string)
#    cursor = cnxn.cursor()
#    
#    
#    #reject checkin
#    sql = ("""  
#    DELETE from ICDCheckIn
#    where ICDParticipantNumber in (?) """)
#    params = (checkIn_pax_id)
#    cursor.execute(sql, params)
#    cnxn.commit()
#    print "Number of rows affected by rejecting checkin: %s" % str(cursor.rowcount)
#    
#    cursor.close()
#    cnxn.close()

#def pxr_job_change_reject(pxr_job_change_pax_id):
#    cnxn = pyodbc.connect(connect_string)
#    cursor = cnxn.cursor()
#    
#    
#    #reject job_change
#    sql = ("""  
#    DELETE from ICDRIPA
#    where ICDParticipantID in (?) """)
#    params = (pxr_job_change_pax_id)
#    cursor.execute(sql, params)
#    cnxn.commit()
#    
#    db_results_num= str(cursor.rowcount)
#    print "Number of rows affected by rejecting job change: %s" % db_results_num
#
#    cursor.close()
#    cnxn.close()
#
#
#


#def address_result(sql, param1):
#    cnxn = pyodbc.connect(connect_string)
#    cursor = cnxn.cursor()
#    
#    params = (param1)
#    cursor.execute(sql ,params)
#    rows = cursor.fetchall()
#    
#    addresses= []
#    #i=0
#    for row in rows:
#        
#        addresses.append(row)
##        print "appending to address list: ", row[i]
##        i+=1
#    #print paxs
#     
#    cursor.close()
#    cnxn.close()
#    return addresses
